﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using System.Reflection;
using Demo.CommonBase.Logging;

namespace Demo.CommonBase.Integration.DAO.Impl
{
    public class DAOBase
    {
        private string default_Connection;
        public DAOBase()
		{
            ConnectionString = "ConnectionString";
		}

        public string ConnectionString
        {
            get { return default_Connection; }
            set { default_Connection = value; }
        }

		/// <summary>
		/// Method Generate connection to SQL
		/// </summary>
		/// <returns></returns>
		/// <remarks></remarks>
		private Database GetDatabase()
		{
			Database _database = default(Database);
			try {
				_database = DatabaseFactory.CreateDatabase(default_Connection);
				return _database;
			} catch (Exception ex) {
				throw ex;
			}
		}

        /// <summary>
        /// Method that gets a list of SQL without parameters
        /// </summary>
        /// <param name="spName">Name store procedure</param>
        /// <returns>Dataset</returns>
        /// <remarks></remarks>
        public DataSet Retrieve(string spName)
        {
            Database db = default(Database);
            DataSet result = null;
            DbCommand cmd = null;
            try
            {

               // Logger.Info();
                db = GetDatabase();
                cmd = db.GetStoredProcCommand(spName);
                result = db.ExecuteDataSet(cmd);
                return result;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                throw ex;
            }
        }

        /// <summary>
        /// Method that gets a list of SQL with parameters
        /// </summary>
        /// <param name="spName">name store procedure</param>
        /// <param name="dbParameters">parameters</param>
        /// <returns>list</returns>
        /// <remarks></remarks>
        public DataSet Retrieve(string spName, IList<IDbDataParameter> dbParameters)
        {
            Database db = default(Database);
            DataSet result = null;
            DbCommand cmd = null;
            try
            {
                //Logger.Info();
                db = GetDatabase();
                cmd = db.GetStoredProcCommand(spName);
                if (dbParameters != null & dbParameters.Count > 0)
                {
                    foreach (IDbDataParameter param in dbParameters)
                    {
                        CheckDbNull(param);
                        cmd.Parameters.Add(param);
                    }
                }
                result = db.ExecuteDataSet(cmd);
                return result;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                throw ex;
            }
        }

        /// <summary>
        /// Method that get value of SQL with parameters
        /// </summary>
        /// <typeparam name="T">type value</typeparam>
        /// <param name="spName">name store procedure</param>
        /// <param name="dbParameters">parameter</param>
        /// <returns>value</returns>
        /// <remarks></remarks>
        public T RetrieveValue<T>(string spName, IList<IDbDataParameter> dbParameters)
        {
            Database db = default(Database);
            DbCommand cmd = null;
            T result = default(T);
            try
            {
               //Logger.Info();
                db = GetDatabase();
                cmd = db.GetStoredProcCommand(spName);
                if (dbParameters != null & dbParameters.Count > 0)
                {
                    foreach (IDbDataParameter param in dbParameters)
                    {
                        CheckDbNull(param);
                        cmd.Parameters.Add(param);
                    }
                }
                result = (T)db.ExecuteScalar(cmd);
                return result;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                throw ex;
            }
        }

        /// <summary>
        /// method that check value is nothig
        /// </summary>
        /// <param name="dbParameters"></param>
        /// <remarks></remarks>
        private void CheckDbNull( IDbDataParameter dbParameters)
        {
            if (dbParameters != null & dbParameters.Value == null)
            {
                dbParameters.Value = DBNull.Value;
            }
        }

        /// <summary>
        /// Method that gets a list of SQL with parameters
        /// </summary>
        /// <param name="spName">name store procedure</param>
        /// <param name="dbParameters">parameters</param>
        /// <returns>list</returns>
        /// <remarks></remarks>

        public int Create(string spName, IList<IDbDataParameter> dbParameters)
        {
            Database db = default(Database);
            DbCommand cmd = null;
            int id;
            try
            {
                ///Logger.Info();
                db = GetDatabase();
                cmd = db.GetStoredProcCommand(spName);

                if (dbParameters != null & dbParameters.Count > 0)
                {
                    foreach (IDbDataParameter param in dbParameters)
                    {
                        CheckDbNull( param);
                        cmd.Parameters.Add(param);
                    }
                }

                id = Convert.ToInt32(db.ExecuteScalar(cmd));

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                throw ex;
            }
            return id;
        }

        /// <summary>
        /// Method that gets a list of SQL with parameters
        /// </summary>
        /// <param name="spName">name store procedure</param>
        /// <param name="dbParameters">parameters</param>
        /// <returns>list</returns>
        /// <remarks></remarks>
        public void Update(string spName, IList<IDbDataParameter> dbParameters)
        {
            Database db = default(Database);
            DbCommand cmd = null;

            try
            {
                //Logger.Info();
                db = GetDatabase();
                cmd = db.GetStoredProcCommand(spName);

                if (dbParameters != null & dbParameters.Count > 0)
                {
                    foreach (IDbDataParameter param in dbParameters)
                    {
                        CheckDbNull(param);
                        cmd.Parameters.Add(param);
                    }
                }

                db.ExecuteNonQuery(cmd);

            }
            catch (Exception ex)
            {
                //Logger.Error(MethodInfo.GetCurrentMethod(), ex);
                throw ex;
            }
        }

        /// <summary>
        /// Valida la estructura del data set.
        /// </summary>
        /// <param name="dataSet">El data set a ser validado.</param>
        /// <returns>True si el data set es válido; false en caso contrario.</returns>
        protected static bool ValidateDataSet(DataSet dataSet)
        {
            bool isValid = true;

            if (dataSet == null)
            {
                isValid = false;
            }
            else if (dataSet.Tables.Count == 0)
            {
                isValid = false;
            }
            else if (dataSet.Tables[0].Rows.Count == 0)
            {
                isValid = false;
            }

            return isValid;
        }

        /// <summary>
        /// Method that gets a list of SQL with parameters
        /// </summary>
        /// <param name="spName">name store procedure</param>
        /// <param name="dbParameters">parameters</param>
        /// <returns>list</returns>
        /// <remarks></remarks>
        public void Delete(string spName, IList<IDbDataParameter> dbParameters)
        {
            Database db = default(Database);
            DbCommand cmd = null;

            try
            {
                //Logger.Info();
                db = GetDatabase();
                cmd = db.GetStoredProcCommand(spName);

                if (dbParameters != null & dbParameters.Count > 0)
                {
                    foreach (IDbDataParameter param in dbParameters)
                    {
                        CheckDbNull(param);
                        cmd.Parameters.Add(param);
                    }
                }

                db.ExecuteNonQuery(cmd);

            }
            catch (Exception ex)
            {
                //Logger.Error(MethodInfo.GetCurrentMethod(), ex);
                throw ex;
            }
        }

    }
}
